set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=20;
set hive.exec.max.dynamic.partitions.pernode=20;
with way_tmp as (
select  input_time
       ,waybill_no
       ,terminal_dispatch_code
       ,receiver_name
       ,receiver_mobile_phone
       ,concat(coalesce(receiver_province_name,''),
               coalesce(receiver_city_name,''),
               coalesce(receiver_area_name,''),
               coalesce(receiver_detailed_address,'')
                  ) as receiver_address
       ,sender_name
       ,sender_mobile_phone
       ,concat(coalesce(sender_province_name,''),
               coalesce(sender_city_name,''),
               coalesce(sender_area_name,''),
               coalesce(sender_detailed_address,'')
                  ) as sender_address
       ,remarks
       ,delivery_time
       ,is_sign
       ,is_delete
       ,is_void
       ,row_number() over(partition by waybill_no order by dt desc) as rank
   from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
 where dt between date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
  and to_date(input_time) between date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
 ),
way as (
select  input_time
       ,waybill_no
       ,terminal_dispatch_code
       ,receiver_name
       ,receiver_mobile_phone
       ,receiver_address
       ,sender_name
       ,sender_mobile_phone
       ,sender_address
       ,remarks
       ,delivery_time
  from way_tmp
    where rank = 1 and is_sign = 0
      and is_delete = 1 and is_void = 0
),
tend as (
  select
       waybill_no
       ,package_name
     from
  jms_dwd.dwd_oms_waybill_extend_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-32) and '{{ execution_date | cst_ds }}'
)
insert overwrite table jms_dm.dm_nosign_waybill_dt partition (dt='{{ execution_date | cst_ds }}')
select  to_date(way.input_time) as input_time
       ,way.waybill_no
       ,way.terminal_dispatch_code
       ,tend.package_name
       ,way.receiver_name
       ,way.receiver_mobile_phone
       ,way.receiver_address
       ,way.sender_name
       ,way.sender_mobile_phone
       ,way.sender_address
       ,way.remarks
       ,way.delivery_time
       , substr(way.receiver_mobile_phone, 1, 3)  pre_3_receiver_mobile
       , substr(way.sender_mobile_phone, 1, 3)    pre_3_sender_mobile
       , substr(way.sender_mobile_phone, 8, 11)   last_4_sender_mobile
       , substr(way.receiver_mobile_phone, 8, 11) last_4_receiver_mobile
  from way
   left join tend
      on way.waybill_no = tend.waybill_no
 distribute by pmod(hash(way.waybill_no),180) ;

